﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Data.OleDb;
namespace DAL
{
        public class dataHelpper
        {
            private static string _sever;

            public static string Sever
            {
                get { return _sever; }
                set { _sever = value; }
            }
            private static string _database;

            public static string Database
            {
                get { return _database; }
                set { _database = value; }
            }
            private static string _user;

            public static string User
            {
                get { return _user; }
                set { _user = value; }
            }
            private static string _pass;

            public static string Pass
            {
                get { return _pass; }
                set { _pass = value; }
            }
            private static SqlConnection _getconnect;

            public static SqlConnection Getconnect1
            {
                get { return _getconnect; }
                set { _getconnect = value; }
            }
            SqlDataAdapter da = new SqlDataAdapter();
            public static bool Options;


            #region" ket noi cua Excel"
            private string _path;

            public string Path
            {
                get { return _path; }
                set { _path = value; }
            }
            private static OleDbConnection _ExcelConnect;

            public static OleDbConnection ExcelConnect
            {
                get { return dataHelpper._ExcelConnect; }
                set { dataHelpper._ExcelConnect = value; }
            }
            #endregion
            public static SqlConnection taoKN()
            {

                if (Options == true)
                {
                    Getconnect1 = new SqlConnection("Server=" + Sever + ";database=" + Database + ";uid=" + User + "; pwd=" + Pass + ";");
                    return Getconnect1;
                }
                else
                {
                    Getconnect1 = new SqlConnection("Server=.; Database=QLTV; Integrated Security=SSPI;");
                    return Getconnect1;
                }

            }
            public static bool ktraKetnoi(string sever, string database, string user, string pass)
            {
                SqlConnection con = taoKN();
                //con.ToString();           
                try
                {
                    con.Open();
                    if (con.State == ConnectionState.Open)
                    {
                        return true;
                    }
                    return false;
                }
                catch { return false; };
            }
            /// public void Mo_KN()
            // {
            //     SqlConnection con = taoKN();
            //     con.Open();
            // }
            //public void Dong_KN()
            //{
            //    SqlConnection con = taoKN();
            //    con.Close();
            //}
            public static bool KT_Ketnoi2()
            {
                SqlConnection con = taoKN();
                con.Open();
                if (con.State == ConnectionState.Open)
                {
                    return true;
                }
                return false;
            }
            public bool KT_ketnoi()
            {
                SqlConnection con = taoKN();
                con.Open();
                if (con.State == ConnectionState.Open)
                {
                    return true;
                }
                return false;
            }

            public void truyvan(string sql)
            {
                SqlConnection con = taoKN();
                con.Open();
                SqlCommand cmd = new SqlCommand(sql, con);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                con.Close();
                //Dong_KN();
            }
            public DataSet HienThi(string sql)
            {
                //Mo_KN();
                //no bao may cho do do

                SqlConnection con = taoKN();
                //con.Open();
                SqlDataAdapter da = new SqlDataAdapter(sql, con);
                DataSet dt = new DataSet();
                da.Fill(dt);
                // con.Close();
                //Dong_KN();
                return dt;
            }
            public int ExecuteCalar(string sql)
            {
                SqlConnection con = taoKN();
                con.Open();
                SqlCommand cmd = new SqlCommand(sql, con);
                int temp;
                try
                {
                    temp = (int)cmd.ExecuteScalar();
                }
                catch { temp = 1; }
                return temp;
            }


            // hàm thêm dữ liệu từ file Excel
            //#region "Thao Tac tren Excel
            //Tra ve mot chuoi ket noi den file Excel
            
            
           
            
            }

 

            

        }

